"""
生成前50名的排名表格
和前50名分别参加的项目
"""
import pandas as pd
from psycopg2 import extras
from flask import Blueprint, g
from util.response import response


ranked_api = Blueprint('ranked', __name__)


@ranked_api.route("/api/ranked", methods=['GET'])
def create_excel():
    """
        生成excel
    """
    result = {}
    with g.db_conn.cursor(cursor_factory=extras.RealDictCursor) as cursor:
        sql = """
            WITH ranked AS (
                SELECT 
                    person_id,
                    COUNT(project_id) AS project_count
                FROM 
                    project_person_relation
                WHERE 
                    project_person_relation.deleted_at IS NULL
                GROUP BY 
                    person_id
                HAVING project_count >= 5
                ORDER BY 
                    project_count DESC
            )
                SELECT 
                    id, id_card, phone, name, project_count
                FROM persons JOIN ranked ON persons.id = ranked.person_id 
                WHERE persons.deleted_at IS NULL
                ORDER BY ranked.project_count DESC
        """

        cursor.execute(sql)
        result = cursor.fetchall()
        df = pd.DataFrame(result)
        df = df.drop('id', axis=1)
        df.rename(columns={'id_card': '身份证号', 'name': '姓名', 'phone': '手机号', 'project_count': '参数评标次数'}, inplace=True)

        with pd.ExcelWriter("static/download/ranked.xlsx") as writer:
            df.to_excel(writer, index=False, sheet_name='排名表')

            df_result = pd.DataFrame(result)
            all_ids = df_result['id'].tolist()

            sub_query = f"""
                SELECT
                    project_name, project_code
                FROM
                    projects ps
                JOIN project_person_relation  pr ON pr.project_id = ps.id
                WHERE pr.person_id in ({','.join(map(str, all_ids))})
            """
            cursor.execute(sub_query)
            res = cursor.fetchall()
            df1 = pd.DataFrame(res)
            df1.rename(columns={'project_name': '项目名称', 'project_code': '项目编号'}, inplace=True)
            df1.to_excel(writer, index=False, sheet_name='项目列表')

            for item in result:
                q = f"""
                    SELECT 
                        project_name, project_code
                    FROM
                        projects ps
                    JOIN project_person_relation  pr ON pr.project_id = ps.id
                    WHERE pr.person_id = {item['id']} 
                """
                cursor.execute(q)
                res = cursor.fetchall()
                df2 = pd.DataFrame(res)
                df2.rename(columns={'project_name': '项目名称', 'project_code': '项目编号'}, inplace=True)
                df2.to_excel(writer, index=False, sheet_name=f'{item["name"]}的项目')
    return response(200, "success", result)
